package com.luobo.pcsdepositmanage.controller;

import com.luobo.pcsdepositmanage.pojo.basic.PcsDepositLogic;
import com.luobo.pcsdepositmanage.pojo.createacsfile.AcsFile;
import com.luobo.pcsdepositmanage.pojo.createacsfile.AcsInOutDescribeResult;
import com.luobo.pcsdepositmanage.pojo.databasetest.PcsDepositFile;
import com.luobo.pcsdepositmanage.pojo.databasetest.PcsInOutDescribe;
import com.luobo.pcsdepositmanage.service.databasetest.PcsDepositFileService;
import com.luobo.pcsdepositmanage.service.databasetest.PcsInOutDescribeService;
import com.luobo.pcsdepositmanage.util.ExcelOperationUtil;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

@RestController
public class exportDataToFile {


    private static final Logger LOGGER = LoggerFactory.getLogger(importDataFromFile.class);

    @Autowired
    private PcsInOutDescribeService pcsInOutDescribeService;
    @Autowired
    private PcsDepositFileService pcsDepositFileService;

    @Value("${filedir.name1}")
    private String filedir;

    private Workbook workbook;


    @Value("${filedir.templatefilename}")
    private String templatefilename;
    @Value("${filedir.resultfilename}")
    private String resultFileName;
    @Value("${createexcle.clonenum}")
    private int cloneNum ;
    @Value("${createexcle.dirnum}")
    private int dirNum ;



    @Value("${filedir.acslistFile}")
    private String acslistFile;
    @Value("${filedir.acslistsheet}")
    private String acslistsheet;
    @Value("${filedir.acslistheadnum}")
    private int acslistheadnum;

    private List<Map<Integer,Object>> acsFileList;
    private Map<String,Object> acsNameMap = new HashMap<>();

    private List<PcsDepositFile> pcsDepositFileList = new ArrayList<>();
    private List<PcsInOutDescribe> pcsInOutDescribeList = new ArrayList<>();

    @RequestMapping("/export")
    public String importDataFromACSFile(){

//        读取数据库数据

//        读取需要处理Pcs服务编码列表From Excel acsFileList.xlsx
        this.acsNameMap = getPcsListFromExcel();
        LOGGER.info("读取PCS服务编码列表：{}",this.acsNameMap.size());

//        生成List<PcsDepositFile>
        this.pcsDepositFileList = createPcsDepositList();
        LOGGER.info("生成PCS服务编码列表：{}",this.pcsDepositFileList.size());

//        生成List<PcsInOutDescribe>
        this.pcsInOutDescribeList = createPcsInOutDescribeList();
        LOGGER.info("生成PCS服务编码输入输出列表：{}",this.pcsInOutDescribeList.size());

        try {
            this.workbook = ExcelOperationUtil.getWorkbookFromExcel(this.filedir+File.separator+this.templatefilename);
        } catch (IOException e) {
            e.printStackTrace();
        }


//        将List<PcsDepositFile>写入workbook对象
//        将List<PcsInOutDescribe>写入workbook对象
//        以getPcsDepositCode为标准对list进行分组
        Map<String, List<PcsInOutDescribe>> collectPcsDepositCode = this.pcsInOutDescribeList.parallelStream().collect(Collectors.groupingBy(PcsInOutDescribe::getPcsDepositCode));

        int i = 0;
        for (PcsDepositFile temppcsDepositFile:
                this.pcsDepositFileList) {
            LOGGER.info("写入第{}个PCS服务编码：{}",i++,temppcsDepositFile.getPcsDepositCode());
            List<PcsInOutDescribe> temppcsInOutDescribe = collectPcsDepositCode.get(temppcsDepositFile.getPcsDepositCode());
            addExcelForPcsInOutDescribe(temppcsDepositFile,temppcsInOutDescribe);
        }

//        将workbook对象写入resultFileName文件
        LOGGER.info("写入文件");
        writeExcel();
        LOGGER.info("文件已成功生成");

        return "Succ";
    }

    private List<PcsInOutDescribe> createPcsInOutDescribeList() {
        List<PcsInOutDescribe> pcsInOutDescribes = new ArrayList<>();
        for (String pcsDepositCode :
                acsNameMap.keySet()) {
            List<PcsInOutDescribe> temppcsInOutDescribes = null;
            if(isCreateAcs(pcsDepositCode)){
                Map<String,String> paraMap = new HashMap<>();
                paraMap.put("pcsDepositCode",pcsDepositCode);
                temppcsInOutDescribes = this.pcsInOutDescribeService.queryDataForMap(paraMap);
            }else {
                continue;
            }
            pcsInOutDescribes.addAll(temppcsInOutDescribes);
        }

        return pcsInOutDescribes;
    }

    private List<PcsDepositFile> createPcsDepositList() {
        List<PcsDepositFile> pcsDepositFiles = new ArrayList<>();

        for (String pcsDepositCode :
                acsNameMap.keySet()) {
            List<PcsDepositFile> tempPcsDepositFiles = null;
            if(isCreateAcs(pcsDepositCode)){
                Map<String,String> paraMap = new HashMap<>();
                paraMap.put("pcsDepositCode",pcsDepositCode);
                tempPcsDepositFiles = this.pcsDepositFileService.queryDataForMap(paraMap);
            }else {
                continue;
            }
            pcsDepositFiles.addAll(tempPcsDepositFiles);
        }

        return pcsDepositFiles;
    }

    public Map<String,Object> getPcsListFromExcel(){
        Map<String,Object> paraMap = new HashMap<>();
        Map<String,Object> resultMap = new HashMap<>();
        try {
            String filename = this.acslistFile;
            String sheetname = this.acslistsheet;
            int headrownum = this.acslistheadnum;
            paraMap.put("filedir",this.filedir);
            paraMap.put("filename",filename);
            paraMap.put("sheetname",sheetname);
            paraMap.put("headrownum",headrownum);

            //            this.acsFileList = EasyExcelUtils.noModelReadEasyExcel(paraMap);
            this.acsFileList = ExcelOperationUtil.readExcelFromFileForMapToListMap(paraMap);

            for (int i = 0; i < this.acsFileList.size(); i++) {
                Object col1Object = this.acsFileList.get(i).get(0);
                Object col2Object = this.acsFileList.get(i).get(1);
                Object col3Object = this.acsFileList.get(i).get(2);
                Object col4Object = this.acsFileList.get(i).get(3);
                Object col5Object = this.acsFileList.get(i).get(4);
                Map<String,String> tempMap = new HashMap<>();

                if(col2Object != null){
                    tempMap.put("acsIsCreate",(String)col2Object);
                }else{
                    tempMap.put("acsIsCreate","否");
                }
                if(col3Object != null){
                    tempMap.put("acsDepositCate",(String)col3Object);
                }else{
                    tempMap.put("acsDepositCate","");
                }
                if(col4Object != null){
                    tempMap.put("acsDepositIdenty",(String)col4Object);
                }else{
                    tempMap.put("acsDepositIdenty","");
                }
                if(col5Object != null){
                    tempMap.put("acsDepositSyscode",(String)col5Object);
                }else{
                    tempMap.put("acsDepositSyscode","");
                }
                if(col1Object != null && col1Object.toString().length()>0){
                    resultMap.put((String)col1Object,tempMap);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return resultMap;
    }

    public boolean isCreateAcs(String pcsDepositCode){
        boolean isCreateFlag = false;

        if(pcsDepositCode!=null && pcsDepositCode.length()>0){
            if(this.acsNameMap.containsKey(pcsDepositCode)){
                Map<String,String> tempString = (Map<String, String>) this.acsNameMap.get(pcsDepositCode);
                if(tempString.get("acsIsCreate").equals("是")){
                    isCreateFlag = true;
                }
            }
        }

        return isCreateFlag;
    }



    public void addExcelForPcsInOutDescribe(PcsDepositFile pcsDepositFilePara,List<PcsInOutDescribe> pcsInOutDescribesPara){


        Sheet operaSheet = this.workbook.getSheetAt(this.dirNum);
        int physicalNumberOfRows = 1;
        Row operaRow = null;

        if(this.workbook.getSheet(pcsDepositFilePara.getPcsDepositCode())!=null){
            LOGGER.info("该sheet页已存在，不再生成");
            return;
        }

        for (int i = 1; i < operaSheet.getPhysicalNumberOfRows(); i++) {
            operaRow = operaSheet.getRow(i);
            if(operaRow == null ||
                    operaRow.getCell(0) == null ||
                    ExcelOperationUtil.getValue(operaRow.getCell(0)) == null ||
                    ExcelOperationUtil.getValue(operaRow.getCell(0)).equals("")){
                physicalNumberOfRows = i;
                break;
            }
        }

        if(operaRow == null){
            operaRow = operaSheet.createRow(physicalNumberOfRows);
        }

        Cell operaCell = null;
//        设置序号
        ExcelOperationUtil.setCellForIndex(operaRow,0,physicalNumberOfRows);
//        设置服务名称
        ExcelOperationUtil.setCellForIndex(operaRow,1,pcsDepositFilePara.getPcsDepositName());
//        设置提供方组件名称
        ExcelOperationUtil.setCellForIndex(operaRow,2,"个人存款组件");
//        设置业务类别
        ExcelOperationUtil.setCellForIndex(operaRow,3,pcsDepositFilePara.getPcsDepositCate());
//        设置服务性质
        ExcelOperationUtil.setCellForIndex(operaRow,4,pcsDepositFilePara.getPcsDepositIdenty());

//        设置服务消费方系统
        ExcelOperationUtil.setCellForIndex(operaRow,7,pcsDepositFilePara.getPcsDepositSyscode());

//        设置超链接及提供方服务标识
        CreationHelper createHelper = new XSSFCreationHelper((XSSFWorkbook) workbook);
        Hyperlink  link1 = createHelper.createHyperlink(HyperlinkType.DOCUMENT);

        link1.setAddress("#"+pcsDepositFilePara.getPcsDepositCode()+"!A1");
        operaCell = operaRow.getCell(6);
        if (operaCell == null){
            operaCell = operaRow.createCell(6);
        }
        operaCell.setHyperlink(link1);
        operaCell.setCellValue(pcsDepositFilePara.getPcsDepositCode());

        operaSheet = this.workbook.cloneSheet(this.cloneNum);
        int operaInt = this.workbook.getSheetIndex(operaSheet);
        String sheetname = pcsDepositFilePara.getPcsDepositCode();
        this.workbook.setSheetName(operaInt,sheetname);
//        List<CellRangeAddress> originMerged = operaSheet.getMergedRegions();



        ExcelOperationUtil.setCellForCoordinate(operaSheet,1,0,pcsDepositFilePara.getPcsDepositName());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,5,3,pcsDepositFilePara.getPcsDepositDescribe());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,6,3,"个人存款组件");
        ExcelOperationUtil.setCellForCoordinate(operaSheet,18,3,pcsDepositFilePara.getPcsDepositSAF());


        ExcelOperationUtil.setCellForCoordinate(operaSheet,19,3,pcsDepositFilePara.getPcsDepositSyscode());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,20,3,"否");

//       通过串行流过滤status!=0的数据
        if(pcsInOutDescribesPara!=null && pcsInOutDescribesPara.size()>0){
            pcsInOutDescribesPara = pcsInOutDescribesPara.stream().filter(r->r.getStatus()==0).collect(Collectors.toList());
        }else{
            LOGGER.info("未成功获取当前pcs服务编码：{}，的输入输出内容",pcsDepositFilePara.getPcsDepositCode());
            return;
        }

//              以getInOutFlag为标准对list进行分组,将输入与输出分开
        Map<Integer, List<PcsInOutDescribe>> collectInOutFlag = pcsInOutDescribesPara.parallelStream().collect(Collectors.groupingBy(PcsInOutDescribe::getInOutFlag));

        List<PcsInOutDescribe> pcsInDescribes = collectInOutFlag.get(0);
        List<PcsInOutDescribe> pcsOutDescribes = collectInOutFlag.get(1);
//        支持file文件接口部分格式的输出
//        List<PcsInOutDescribe> pcsFileDescribes = collectInOutFlag.get(2);

//
//        for (PcsInOutDescribe tempPcsInOutDescribe:
//             pcsInOutDescribesPara) {
//            if(tempPcsInOutDescribe.getInOutFlag() == 0 && tempPcsInOutDescribe.getStatus() == 0){
//                pcsInDescribes.add(tempPcsInOutDescribe);
//            }else if(tempPcsInOutDescribe.getInOutFlag() == 1 && tempPcsInOutDescribe.getStatus() == 0){
//                pcsOutDescribes.add(tempPcsInOutDescribe);
//            }
//        }


        int l1 = 0;
        int l2 = 0;
        if(pcsInDescribes!=null && pcsInDescribes.size()>0){
//        按照OrderNum升序排列

            pcsInDescribes.sort(Comparator.comparing(PcsInOutDescribe::getOrderNum));
            l1 = intOutAreaWriteForAcsFile(operaSheet,29,pcsInDescribes.size(),0,pcsInDescribes);
        }
        if(pcsOutDescribes!=null && pcsOutDescribes.size()>0){
//        按照OrderNum升序排列
            pcsOutDescribes.sort(Comparator.comparing(PcsInOutDescribe::getOrderNum));
            l2 = intOutAreaWriteForAcsFile(operaSheet,43,pcsOutDescribes.size(),l1,pcsOutDescribes);
        }

//       支持file文件接口部分格式的输出
//        if(pcsFileDescribes!=null && pcsFileDescribes.size()>0){
////        按照OrderNum升序排列
//            pcsFileDescribes.sort(Comparator.comparing(PcsInOutDescribe::getOrderNum));
//            l2 = intOutAreaWriteForAcsFile(operaSheet,61,pcsFileDescribes.size(),l2,pcsFileDescribes);
//        }

    }

    public int intOutAreaWriteForAcsFile(Sheet operaSheet, int flagNum , int overNum, int anyNum, List<PcsInOutDescribe> pcsInOutDescribesPara){
        Row operaRow = null;
        Cell operaCell = null;
        int l1 = 0;
//        flagNum = 29|43
        if(overNum>9){
            operaSheet.shiftRows( flagNum+anyNum+9, operaSheet.getLastRowNum()+1, overNum-9, true, false);
        }

        for (int i = flagNum+anyNum; i <flagNum+anyNum+overNum; i++) {
            if(i > flagNum+8+anyNum){

//                operaSheet.shiftRows( i, operaSheet.getLastRowNum()+1, 1, true, false);
                l1++;

                operaRow = operaSheet.createRow(i);
                operaRow.setRowStyle(operaSheet.getRow(30).getRowStyle());
            }else {
                operaRow = operaSheet.getRow(i);
            }

            operaRow.setHeight(operaSheet.getRow(i-1).getHeight());
            for (int j = 1; j < 10; j++) {
                operaCell = operaRow.getCell(j);
                if(operaCell == null){
                    operaCell = operaRow.createCell(j);
                    operaCell.setCellStyle(operaSheet.getRow(i-1).getCell(j).getCellStyle());
//                    System.out.println(operaCell.getStringCellValue());
                }
                PcsInOutDescribe pcsInOutDescribe = pcsInOutDescribesPara.get(i-flagNum-anyNum);
                String tempString = null;
                switch (j){
                    case 1:
                        tempString = String.valueOf(pcsInOutDescribe.getOrderNum());
                        break;
                    case 2:
                        tempString = pcsInOutDescribe.getEnName();
                        break;
                    case 3:
                        tempString = pcsInOutDescribe.getChName();
                        break;
                    case 4:
                        tempString = pcsInOutDescribe.getDataDictCode();
                        break;
                    case 5:
                        tempString = pcsInOutDescribe.getDataDictTypeDescribe();
                        break;
                    case 6:
                        tempString = pcsInOutDescribe.getDataType();
                        break;
                    case 7:
                        tempString = pcsInOutDescribe.getIsRequired();
                        break;
                    case 8:
                        tempString = pcsInOutDescribe.getAttributeDescribe();
                        break;
                    case 9:
                        tempString = pcsInOutDescribe.getRemarkDescribe();
                        break;
                }
                if(tempString != null){
                    operaCell.setCellValue(tempString);
                }else{
                    continue;
                }
            }

        }
        return  l1 ;

    }


    /*
    执行写入excel文件任务
     */
    public void writeExcel(){
        try {
            ExcelOperationUtil.writeWorkbookToFile(this.workbook,this.filedir+File.separator+this.resultFileName);
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.info(e.getStackTrace().toString());

        }
    }


}
